package com.stj.dao;

import com.stj.entity.Blog;
import com.stj.entity.Link;
import com.stj.entity.blogType;
import com.stj.entity.comment;
import com.stj.util.JdbcUtil;
import com.sun.org.apache.bcel.internal.generic.RETURN;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AdminBlogDao {
    Connection conn = JdbcUtil.getConnection();
    public List getBlogAndBlogTypeList() {
        ArrayList list =new ArrayList();
        try {
            String sql = "SELECT * FROM `t_blog`,t_blogtype WHERE t_blog.typeId = t_blogtype.id ";
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                /*博文信息*/
                Blog blog = new Blog(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4), rs.getInt(5), rs.getInt(6), rs.getString(7));
                /*博文类型*/
                blogType blogType = new blogType(rs.getInt("id"), rs.getString("typeName"), rs.getInt("orderNo"));
                blog.setBlogType(blogType);
                list.add(blog);
            }
        } catch (Exception e) {
            System.out.println("AdminBlogDao ->getBlogAndBlogTypeList 方法错误"+e);
        }
        return  list;
    }

    public int  deleteBlog(int id) {
        int i=0 ;
        try{
            String sql ="delete from t_blog where id ="+id;
            PreparedStatement pst = conn.prepareStatement(sql);
            i = pst.executeUpdate();
        }catch (Exception e) {
            System.out.println("AdminBlogDao ->deleteBlog 方法错误"+e);
        }
        return  i;
    }

    public Blog updateBlogFirst(int id) {
        try {
            String sql ="SELECT * FROM `t_blog`,t_blogtype WHERE t_blog.typeId = t_blogtype.id AND t_blog.id="+id;
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs =pst.executeQuery();
            if(rs.next()){
                Blog blog =new Blog(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDate(4), rs.getInt(5), rs.getInt(6), rs.getString(7));
                blogType blogType = new blogType(rs.getInt("id"), rs.getString("typeName"), rs.getInt("orderNo"));
                blog.setBlogType(blogType);
                return blog;
            }

        }catch (Exception e){
            System.out.println("AdminBlogDao ->updateBlogFirst 方法错误"+e);
        }
        return  null;
    }
    /*修改*/
    public int updateBlog(Blog blog) {
        int i=0;
        try {
            String sql ="update t_blog set title=?, summary=?,content=?, typeId=? WHERE id ="+blog.getId();
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,blog.getTitle());
            pst.setString(2,blog.getSummary());
            pst.setString(3,blog.getContent());
            pst.setInt(4,blog.getTypeId());
             i = pst.executeUpdate();

        }catch (Exception e){
            System.out.println("AdminBlogDao ->updateBlog 方法错误"+e);
        }
         return  i;
    }

    public List findBlogType() {
        List  blogTypes = new ArrayList<>();
        try {
            Connection conn = JdbcUtil.getConnection();
            String sql = "SELECT id,typeName,orderNo,(SELECT COUNT(*) FROM t_blog WHERE typeId = t_blogtype.`id`) blogCount FROM t_blogtype ORDER BY orderNo";
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()){
                blogType blogType = new blogType(rs.getInt(1),rs.getString(2),rs.getInt(3));
                blogType.setBlogCount(rs.getInt("blogCount"));
                blogTypes.add(blogType);
            }
        } catch (Exception e) {
            System.out.println("AdminBlogDao ->findBlogType 方法错误"+e);
        }
        return blogTypes;
    }

    public List selectCommentByState(int state) {
        List commentList = new ArrayList();
        try {
            Connection conn = JdbcUtil.getConnection();
            String sql = null;
            if (state==0){
                sql = "SELECT c.*,b.`title` FROM t_comment c ,t_blog b WHERE c.`blogId`=b.`id` AND state=? ORDER BY c.`blogId`";
            }else {
                sql = "SELECT c.*,b.`title` FROM t_comment c ,t_blog b WHERE c.`blogId`=b.`id` AND state!=0 ORDER BY c.`blogId`";
            }
            PreparedStatement pst = conn.prepareStatement(sql);
            if (state==0){
                pst.setInt(1,state);
            }
            ResultSet rst = pst.executeQuery();
            while (rst.next()){
                comment comment = new comment(rst.getInt(1),rst.getString(2),rst.getInt(3),
                        rst.getString(4),rst.getDate(5),rst.getInt(6));
                Blog blog = new Blog();
                blog.setTitle(rst.getString("title"));
                comment.setBlog(blog);
                commentList.add(comment);
            }
        } catch (Exception e) {
            System.out.println("AdminBlogDao ->selectCommentByState 方法错误"+e);
        }
        return commentList;
    }
    /*审核评论*/
    public int setCommentStateOne(List<Integer> del_ids) {
        try {
            int result = 0;
            Connection conn = JdbcUtil.getConnection();
            for (int id : del_ids){
                String sql = "update t_comment set state = 1 where id="+id;
                PreparedStatement pst = conn.prepareStatement(sql);
                pst.executeUpdate();
                result++;
            }
            return result;
        } catch (Exception e) {
            System.out.println("AdminBlogDao ->setCommentStateOne 方法错误"+e);
        }
        return 0;
    }
    /*审核不通过*/
    public int setCommentStateTwo(List<Integer> del_ids) {
        try {
            int result = 0;
            Connection conn = JdbcUtil.getConnection();
            for (int id : del_ids){
                String sql = "update t_comment set state = 2 where id="+id;
                PreparedStatement pst = conn.prepareStatement(sql);
                pst.executeUpdate();
                result++;
            }
            return result;
        } catch (Exception e) {
            System.out.println("AdminBlogDao ->setCommentStateTwo 方法错误"+e);
        }
        return 0;
    }
    /**删除评论功能*/
    public int deleteCommentByIds(List<Integer> del_ids) {
        try {
            int result = 0;
            Connection conn = JdbcUtil.getConnection();
            for (int id : del_ids){
                String sql = "delete from t_comment where id="+id;
                PreparedStatement pst = conn.prepareStatement(sql);
                pst.executeUpdate();
                result++;
            }
            return result;
        } catch (Exception e) {
            System.out.println("AdminBlogDao ->deleteCommentByIds 方法错误"+e);
        }
        return 0;

    }
    /*友情链接*/
    public List<Link> selectAllLink() {
        List<Link> linkList = new ArrayList<>();
        try {
            Connection conn = JdbcUtil.getConnection();
            String sql = "select * from t_link order by orderNo";
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rst = pst.executeQuery();
            while (rst.next()){
                Link link = new Link(rst.getInt(1),rst.getString(2),
                        rst.getString(3),rst.getInt(4));
                linkList.add(link);
            }
        } catch (Exception e) {
            System.out.println("AdminBlogDao ->selectAllLink 方法错误"+e);
        }
        return linkList;
    }
    /*修改用户名密码*/
    public int updateBlogPassword(String userName, String newPassword) {
        try {
            Connection conn = JdbcUtil.getConnection();
            String sql = "update t_blogger set password=? where userName=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,newPassword);
            pst.setString(2,userName);
            int result = pst.executeUpdate();
            return result;
        } catch (Exception e) {
            System.out.println("AdminBlogDao ->updateBlogPassword 方法错误"+e);
        }
        return 0;

    }
    /*博客类别管理页面 添加*/
    public int addBlogType(String typeName, int orderNo) {
        int i=0;
        try {
            Connection conn = JdbcUtil.getConnection();
            String sql = "INSERT INTO t_blogtype(typeName,orderNo)VALUES(?,?)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,typeName);
            pst.setInt(2,orderNo);
           i = pst.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    public int updateBlogTypeById(blogType blogType) {
        try {
            Connection conn = JdbcUtil.getConnection();
            String sql = "update t_blogtype set typeName=?,orderNo=? where id=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,blogType.getTypeName());
            pst.setInt(2,blogType.getOrderNo());
            pst.setInt(3,blogType.getId());
            int i = pst.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    public int deleteBlogTypeByIds(List<Integer> ids) {
        try {
            int result = 0;
            Connection conn = JdbcUtil.getConnection();
            for (int id : ids){
                String sql = "delete from t_blogtype where id="+id;
                PreparedStatement pst = conn.prepareStatement(sql);
                pst.executeUpdate();
                result++;
            }
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}
